CREATE PROCEDURE [dbo].[asi_RebuildCounters]
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Activity'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Activity), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Activity'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Activity_Attach'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(ATTACH_SEQN), 1) FROM Activity_Attach), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Activity_Attach'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Batch'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
IF EXISTS (SELECT 1 FROM System_Params WHERE ParameterName = 'AR_Control.UseBatches' AND ShortValue = '2')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(BATCH_NUM), 1) FROM Batch), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Batch'
END
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cert_Register'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Cert_Register), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cert_Register'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Discussion_Forums'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(FORUM_SEQ), 1) FROM Cmty_Discussion_Forums), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Discussion_Forums'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Discussion_Posts'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(POST_SEQ), 1) FROM Cmty_Discussion_Posts), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Discussion_Posts'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Discussion_Posts'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(TOPIC_SEQ), 1) FROM Cmty_Discussion_Posts), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Discussion_Topics'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Discussion_Forums'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(FORUM_SEQ), 1) FROM Cmty_Discussion_Forums), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Forum'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_News'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(NEWS_SEQ), 1) FROM Cmty_News), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_News'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Shared_Files'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(FILE_SEQ), 1) FROM Cmty_Shared_Files), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Shared_Files'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cmty_Shared_Folders'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(FOLDER_SEQ), 1) FROM Cmty_Shared_Folders), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Cmty_Shared_Folders'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Comment_Log'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(COMMENT_LOG_NUM), 1) FROM Comment_Log), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Comment_Log'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Community'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(COMMUNITY_SEQ), 1) FROM Community), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Community'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Content_Pages'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(PAGE_NUM), 1) FROM Content_Pages), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Content_Pages'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Country_Addr_Layouts'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(ADDRESS_FORMAT), 1) FROM Country_Addr_Layouts), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Country_Addr_Layouts'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Exhb_Form_Master'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT MAX(CAST(SEQN AS INT))
FROM (SELECT FORM_ID as SEQN FROM Exhb_Form_Master WHERE ISNUMERIC(FORM_ID) = 1 UNION
SELECT PRODUCT_ID as SEQN FROM Exhb_Prod_Mast WHERE ISNUMERIC(PRODUCT_ID) = 1) AS Q
), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Exhb_Form_Master'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ExpenseItem'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(EXPENSE_SEQN), 1) FROM ExpenseItem), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Expense'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Hotel_Log'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(HOTEL_LOG_NUMBER), 1) FROM Hotel_Log), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'HotelLog'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Invoice'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(INVOICE_NUM), 1) FROM Invoice), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Invoice'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Invoice'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(REFERENCE_NUM), 1) FROM Invoice), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Invoice_Ref'
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(Trans.INVOICE_REFERENCE_NUM), 1) FROM Trans), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Invoice_Ref' AND (SELECT COALESCE(MAX(Trans.INVOICE_REFERENCE_NUM), 1) FROM Trans) > LAST_VALUE
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Job_Record'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(JobId), 1) FROM Job_Record), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Job_Record'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Name'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(CAST(ID AS INT)), 1) FROM [Name] WHERE ISNUMERIC(ID) = 1), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Name'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Name_Address'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(ADDRESS_NUM), 1) FROM Name_Address), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Name_Address'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Name_MatchPlan'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Name_MatchPlan), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Name_MatchPlan'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Name_Note'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(NOTE_NUM), 1) FROM Name_Note), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Name_Note'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Name_Picture'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(PICTURE_NUM), 1) FROM Name_Picture), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Name_Picture'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(ORDER_NUMBER), 1) FROM Orders), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Orders'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product_Kit'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Product_Kit), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Product_Kit'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product_Substitute'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Product_Substitute), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Product_Substitute'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product_Trans'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(PTRANS_NUMBER), 1) FROM Product_Trans), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Product_Trans'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product_Trans'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(TRANS_GROUP_NUMBER), 1) FROM Product_Trans), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'ProductGroupTrans'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Prospect'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COUNT(1) FROM Prospect), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Prospect'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Receipt'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(Auto_Receipt_Num), 1) FROM Receipt), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Receipt'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Receipt'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(ID), 1) FROM Receipt), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Receipt_ID'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Ref_Client'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(CAST(CLIENT_ID AS INT)), 1) FROM Ref_Client WHERE ISNUMERIC(CLIENT_ID) = 1), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Ref_Client'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Referral'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(CAST(REFERRAL_ID AS INT)), 1) FROM Referral WHERE ISNUMERIC(REFERRAL_ID) = 1), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Referral'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Relationship'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM Relationship), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Relationship'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Service_Request'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(CAST(ServiceReference AS INT)), 1) FROM Service_Request WHERE ISNUMERIC(ServiceReference) = 1), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Service_Request'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Stored_List'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(NumberSequence), 1) FROM Stored_List), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Stored_List'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Trans'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(TRANS_NUMBER), 1) FROM Trans), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Trans'
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'User_Tab_Pref_Detail'
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(PREF_ID), 1) - 1 FROM User_Tab_Pref_Detail), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'TabProfile'
END
DECLARE @tableName nvarchar(50)
DECLARE @sql nvarchar(1000)
DECLARE theCursor CURSOR FORWARD_ONLY FOR
SELECT TABLE_NAME FROM UD_Table WHERE ALLOW_MULTIPLE_INSTANCES = 1
OPEN theCursor
FETCH NEXT FROM theCursor INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName
AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')
BEGIN
SET @sql = 'UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(SEQN), 1) FROM ' +
@tableName + '), LAST_UPDATED = getdate(), UPDATED_BY = ''MANAGER''
WHERE COUNTER_NAME = ''' + @tableName + ''''
EXEC (@sql)
END
FETCH NEXT FROM theCursor INTO @tableName
END
CLOSE theCursor
DEALLOCATE theCursor
END
GO
[dbo].[asi_RebuildCounters]